﻿using System.Data.SQLite;
using DVDTek_Data_Object.Collection;
using DVDTek_Data_Object.DataObject;

namespace DVDTek_Data_Object.Accessor
{
    internal class MovieAccessor
    {
        #region Constants
        internal const string TABLE_NAME = "Movie";
        internal const string FN_PRIMARY_KEY = "id";
        private const string FN_ID = "id";
        private const string FN_TITLE = "title";
        private const string FN_DIRECTOR_ID = "director_id";
        private const string FN_ORIGIN = "origin";
        private const string FN_YEAR = "year";
        private const string FN_SEEN = "seen";
        private const string FN_ACQUIRED = "acquired";
        private const string FN_AVAILABLE = "available";
        private const string FN_SUPPORT = "support";
        #endregion

        #region Members
        private static MovieAccessor movieAccessor;
        internal static MovieAccessor Instance
        {
            get
            {
                if (movieAccessor == null)
                {
                    movieAccessor = new MovieAccessor();
                }
                return movieAccessor;
            }
        }

        internal string CreateTableQuery
        {
            get
            {
                return string.Concat(
                    "CREATE TABLE ", TABLE_NAME, " (",
                    FN_ID, " integer PRIMARY KEY AUTOINCREMENT NOT NULL, ",
                    FN_TITLE, " varchar(255) NOT NULL, ",
                    FN_DIRECTOR_ID, " integer NULL, ",
                    FN_ORIGIN, " varchar(30) NULL, ",
                    FN_YEAR, " integer(4) NULL, ",
                    FN_SEEN, " bool NOT NULL, ",
                    FN_ACQUIRED, " bool NOT NULL, ",
                    FN_AVAILABLE, " bool NOT NULL ",
                    FN_SUPPORT, " varchar(20) ",
                    "CONSTRAINT fk_", FN_DIRECTOR_ID, " FOREIGN KEY(", FN_DIRECTOR_ID, ")",
                    " REFERENCES ", DirectorAccessor.TABLE_NAME, "(", DirectorAccessor.FN_PRIMARY_KEY, ")",
                    "CONSTRAINT un_", FN_TITLE, "_", FN_DIRECTOR_ID,
                    " UNIQUE(", FN_TITLE, ",", FN_DIRECTOR_ID, "));");
            }
        }
        #endregion

        #region Constructors
        private MovieAccessor()
        {
        }
        #endregion

        #region Insert / Update / Delete
        internal int Insert(string title, int directorId, string origin, int year,
            bool seen, bool acquired, bool available, string support)
        {
            string query = string.Concat(
                "INSERT INTO ", TABLE_NAME,
                " (", FN_ID, ",", FN_TITLE, ",", FN_DIRECTOR_ID,
                ",", FN_ORIGIN, ",", FN_YEAR,
                ",", FN_SEEN, ",", FN_ACQUIRED,
                ",", FN_AVAILABLE, ",", FN_SUPPORT, ")",
                " VALUES ",
                "('', '", Common.QuoteEscape(title), "','", Common.ValueOrDBNull(directorId), "',",
                "'", Common.QuoteEscape(origin), "','", Common.ValueOrDBNull(year), "',",
                "'", Common.BoolToInt(seen), "',", Common.BoolToInt(acquired), "',",
                "'", Common.BoolToInt(available), "','", Common.QuoteEscape(support), "');",
                "SELECT last_insert_rowid();");

            return (int)Common.ExecuteScalarQuery(query);
        }

        internal void Update(int id, string title, int directorId, string origin, int year,
            bool seen, bool acquired, bool available, string support)
        {
            string query = string.Concat(
                "UPDATE ", TABLE_NAME, " SET ",
                FN_TITLE, "='", Common.QuoteEscape(title), "'",
                FN_DIRECTOR_ID, "='", Common.ValueOrDBNull(directorId), "'",
                FN_ORIGIN, "='", Common.QuoteEscape(origin), "'",
                FN_YEAR, "='", Common.ValueOrDBNull(year), "'",
                FN_SEEN, "='", Common.BoolToInt(seen), "'",
                FN_ACQUIRED, "='", Common.BoolToInt(acquired), "'",
                FN_AVAILABLE, "='", Common.BoolToInt(available), "'",
                FN_SUPPORT, "='", Common.QuoteEscape(support), "'",
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(int id)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }
        #endregion

        #region Select
        internal MovieDataObject Select(int id)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
            {
                int directorId = -1;
                int year = -1;

                if (!reader.IsDBNull(2))
                    directorId = reader.GetInt32(2);
                if (!reader.IsDBNull(4))
                    year = reader.GetInt32(4);

                return new MovieDataObject(reader.GetInt32(0), reader.GetString(1), directorId,
                    reader.GetString(3), year, reader.GetBoolean(5),
                    reader.GetBoolean(6), reader.GetBoolean(7), reader.GetString(8));
            }
            else
                return null;
        }

        internal MovieDataObject Select(string title)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_TITLE, "='", Common.QuoteEscape(title), "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
            {
                int directorId = -1;
                int year = -1;

                if (!reader.IsDBNull(2))
                    directorId = reader.GetInt32(2);
                if (!reader.IsDBNull(4))
                    year = reader.GetInt32(4);

                return new MovieDataObject(reader.GetInt32(0), reader.GetString(1), directorId,
                    reader.GetString(3), year, reader.GetBoolean(5),
                    reader.GetBoolean(6), reader.GetBoolean(7), reader.GetString(8));
            }
            else
                return null;
        }

        internal MovieCollection SelectAll()
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            MovieCollection movies = new MovieCollection();

            while (reader.Read())
            {
                int directorId = -1;
                int year = -1;

                if (!reader.IsDBNull(2))
                    directorId = reader.GetInt32(2);
                if (!reader.IsDBNull(4))
                    year = reader.GetInt32(4);

                movies.Add(new MovieDataObject(reader.GetInt32(0), reader.GetString(1), directorId,
                    reader.GetString(3), year, reader.GetBoolean(5),
                    reader.GetBoolean(6), reader.GetBoolean(7), reader.GetString(8)));
            }

            return movies;
        }

        internal MovieCollection SelectAll(int seen, int acquired, int available)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME);

            string where = " WHERE ";

            if (seen != 0)
            {
                if (seen > 0)
                    query = string.Concat(query, where, FN_SEEN, "='", 1, "'");
                else
                    query = string.Concat(query, where, FN_SEEN, "='", 0, "'");

                where = " AND ";
            }

            if (acquired != 0)
            {
                if (acquired > 0)
                    query = string.Concat(query, where, FN_SEEN, "='", 1, "'");
                else
                    query = string.Concat(query, where, FN_SEEN, "='", 0, "'");

                where = " AND ";
            }

            if (available != 0)
            {
                if (available > 0)
                    query = string.Concat(query, where, FN_SEEN, "='", 1, "'");
                else
                    query = string.Concat(query, where, FN_SEEN, "='", 0, "'");
            }

            query = string.Concat(query, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            MovieCollection movies = new MovieCollection();

            while (reader.Read())
            {
                int directorId = -1;
                int year = -1;

                if (!reader.IsDBNull(2))
                    directorId = reader.GetInt32(2);
                if (!reader.IsDBNull(4))
                    year = reader.GetInt32(4);

                movies.Add(new MovieDataObject(reader.GetInt32(0), reader.GetString(1), directorId,
                    reader.GetString(3), year, reader.GetBoolean(5),
                    reader.GetBoolean(6), reader.GetBoolean(7), reader.GetString(8)));
            }

            return movies;
        }
        #endregion

        internal bool Exist(string title)
        {
            return Select(title) != null;
        }
    }
}
